SQL Server BCP Extract

FastStats Designer supports BCP extracts from SQL Server. In some cases the BCP extract process can be up to 6.5x faster than the standard extract process.

To use BCP extracts:

  1. The data source must be SQL Server 2005 or later

  2. One of the following must be installed:

    1. SQL Server Native Client v11

    2. ODBC Driver 17 or 18 for SQL Server (x64): learn.microsoft.com/en-us/sql/connect/odbc/download-odbc-driver-for-sql-server?view=sql-server-ver17

  3. The connection provider type must be ODBC

  4. The connection string must be specified to use the native client. E.g. the connection string must contain "Driver={Sql Server Native Client 11.0}" or “Driver={ODBC Driver 17 for SQL Server}” or “Driver={ODBC Driver 18 for SQL Server}”

When using BCP extracts the following features are not supported:

  1. Composite fields (composite fields can be constructed as part of the query)

  2. Multi-line custom queries (only 1 statement is allowed in a BCP query)

  3. Control characters are not stripped during the extract process so you must ensure that the source tables only contain 'clean' data

BCP Clean Option

After the initial BCP extract the file has to be cleaned to convert NUL ASCII characters to empty fields. ASCII NUL characters are produced in the extract file when the source field contains empty values. Database NULL values are extracted as empty strings while database empty strings are extracted as ASCII NUL characters which must be clean before FastStats Designer can process them.

There are different clean methods that offer different performance characteristics:

Standard: Reads and modifies each block in turn. None sequential disk access. No additional disk space required.

Copy: Reads extracted file and writes out clean version. Sequential disk access only. Additional disk space required.

Memory Mapped: Maps the entire file in memory and adjusts bytes. No additional disk space required. Disk access depends on OS.

None: Assume file is already clean and do not perform any clean. Only use if BCP source fields do not contain any blank characters.